﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Xml;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Globalization;
// Based off CDC's Epi Info 7 AccessDatabase class; see http://epiinfo.codeplex.com

namespace NutriStat.Data
{
    public class AccessDatabase : NutriStat.Data.OleDbDatabase
    {
        #region Constructors

        public AccessDatabase()
            : base()
        {

        }
        #endregion Constructors

        #region Database Implementation

        public override void SetDataSourceFilePath(string filePath)
        {
            this.ConnectionString = filePath;
        }

        public override string FullName // Implements Database.FullName
        {
            get
            {
                return "[MS Access] " + Location ?? "";
            }
        }
        
        public override void CreateTable(string tableName, List<TableColumn> columns)
        {

            StringBuilder sb = new StringBuilder();

            sb.Append("create table ");
            sb.Append(tableName);
            sb.Append(" ( ");
            foreach (TableColumn column in columns)
            {
                sb.Append("[");
                sb.Append(column.Name);
                sb.Append("]");
                sb.Append(" ");
                if (column.IsIdentity)
                {
                    sb.Append(" COUNTER ");
                }
                else
                {
                    sb.Append(GetDbSpecificColumnType(column.DataType));
                }

                if (column.Length != null)
                {
                    if (GetDbSpecificColumnType(column.DataType).Equals("text"))
                    {
                        sb.Append("(");
                        sb.Append(column.Length.Value.ToString());
                        sb.Append(") ");
                    }
                }
                if (!column.AllowNull)
                {
                    sb.Append(" NOT ");
                }
                sb.Append(" null ");
                if (column.IsPrimaryKey)
                {
                    sb.Append(" constraint ");
                    sb.Append(" PK_");
                    sb.Append(column.Name);
                    sb.Append("_");
                    sb.Append(tableName);
                    sb.Append(" primary key ");
                }
                if (!string.IsNullOrEmpty(column.ForeignKeyColumnName) && !string.IsNullOrEmpty(column.ForeignKeyTableName))
                {
                    sb.Append(" references ");
                    sb.Append(column.ForeignKeyTableName);
                    sb.Append("([");
                    sb.Append(column.ForeignKeyColumnName);
                    sb.Append("]) ");
                    if (column.CascadeDelete)
                    {
                        sb.Append(" on delete cascade");
                    }
                }
                sb.Append(", ");
            }
            sb.Remove(sb.Length - 2, 2);
            sb.Append(") ");

            ExecuteNonQuery(CreateQuery(sb.ToString()));

        }
        #endregion

        #region Private Members

        public override string GetDbSpecificColumnType(GenericDbColumnType dataType)
        {
            switch (dataType)
            {
                case GenericDbColumnType.AnsiString:
                case GenericDbColumnType.AnsiStringFixedLength:
                    return AccessColumnType.Text;

                case GenericDbColumnType.Binary:
                case GenericDbColumnType.Image:
                    return "binary"; // "binary";

                case GenericDbColumnType.Boolean:
                    return AccessColumnType.YesNo; // "yesno";

                case GenericDbColumnType.Byte:
                    return "byte";

                case GenericDbColumnType.Currency:
                    return AccessColumnType.Currency; // "CURRENCY";

                case GenericDbColumnType.Date:
                case GenericDbColumnType.DateTime:
                case GenericDbColumnType.Time:
                    return "datetime";

                case GenericDbColumnType.Decimal:
                case GenericDbColumnType.Double:
                    return "double";

                case GenericDbColumnType.Guid:
                    return "GUID";

                case GenericDbColumnType.Int16:
                case GenericDbColumnType.UInt16:
                    return "SHORT";

                case GenericDbColumnType.Int32:
                case GenericDbColumnType.UInt32:
                    return "integer";

                case GenericDbColumnType.Int64:
                case GenericDbColumnType.UInt64:
                    return "LONG";

                case GenericDbColumnType.Object:
                    return "LONGBINARY";

                case GenericDbColumnType.SByte:
                    return "byte";

                case GenericDbColumnType.Single:
                    return "single";

                case GenericDbColumnType.String:
                case GenericDbColumnType.StringFixedLength:
                    return "text";

                case GenericDbColumnType.StringLong:
                case GenericDbColumnType.Xml:
                    return "MEMO";

                case GenericDbColumnType.VarNumeric:
                    return "double";

                default:
                    throw new Exception("Generic DB Column Type is unknown");
                    //throw new GeneralException("genericDbColumnType is unknown");
                // return "text";
            }
        }
        
        public override string ConnectionDescription
        {
            get { return "Microsoft Access Database: " + Location; }
        }
        
        public override string ConnectionString
        {
            get
            {
                return base.ConnectionString;
            }
            set
            {
                string connectionString;
                //if (Util.IsFilePath(value))
                if(File.Exists(value))
                {
                    connectionString = AccessDatabase.BuildConnectionString(value, "");
                }
                else
                {
                    connectionString = value;
                }

                //we can't test the connection now because the database
                // may not be existant (yet)
                base.ConnectionString = connectionString;
            }
        }

        #endregion
        

        public static string BuildConnectionString(string filePath, string password)
        {
            StringBuilder builder = new StringBuilder();

            //if (filePath.EndsWith(".mdb", true, System.Globalization.CultureInfo.InvariantCulture))
            //{
            //    builder.Append("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=");
            //}
            //else if (filePath.EndsWith(".accdb", true, System.Globalization.CultureInfo.InvariantCulture))
            //{
                builder.Append("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=");
            //}

            builder.Append(EncodeOleDbConnectionStringValue(filePath));
            builder.Append(";");

            builder.Append("User Id=admin;Password=;");

            if (!string.IsNullOrEmpty(password))
            {
                builder.Append("Jet OLEDB:Database Password=");
                builder.Append(EncodeOleDbConnectionStringValue(password));
                builder.Append(";");
            }


            // sanity check
            OleDbConnectionStringBuilder connectionBuilder = new OleDbConnectionStringBuilder(builder.ToString());
            return connectionBuilder.ToString();
        }

        
        public static string BuildDefaultConnectionString(string databaseName)
        {
            //return BuildConnectionString(Configuration.GetNewInstance().Directories.Project + "\\" + databaseName + ".mdb", string.Empty);
            return "";
        }       
       
        public override string IdentifyDatabase()
        {
            return "ACCESS";
        }
    }
}
